package com.bsth.archive.util;

import java.io.IOException;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

public class DBUtil {
	// 互联网
	//private static String				url			= "jdbc:oracle:thin:@192.168.20.8:1521:ioradb";
	// 政务网
	private static String url = "jdbc:oracle:thin:@192.68.16.8:1521:orcl";
	private static String name = "archive2";
	private static String password = "cjpdwxpdda2010_";
	private static String className = "oracle.jdbc.driver.OracleDriver";
	private static Connection oracleConn = null;
	private static ResultSet rs = null;
	// 总条数
	private static int allCount = 35;
	// 分批条数
	private static int preCount = 2;
	// 计数器
	private static int count = 0;
	private static String insertSQL = "insert into zhouqin(firstname, lastname, age) values(?, ?, ?)";
	private static PreparedStatement oraclePs = null;
	private static SimpleDateFormat sdf = new SimpleDateFormat(
			"yyyy-MM-dd");
	;


	/**
	 * 连接数据库
	 *
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public static Connection getConnection2() throws SQLException,
			ClassNotFoundException {
		return DriverManager.getConnection(url, name, password);// 连接数据库
	}

	/**
	 * 释放资源
	 *
	 * @param rs
	 * @param ps
	 * @param conn
	 * @throws Exception
	 */
	public static void release(ResultSet rs, PreparedStatement ps,
							   Connection conn) throws Exception {
		try {
			if (rs != null)
				rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
//			MyLog.loggingError(e.getMessage());
//			MyLog.logging(e.getMessage());
			throw new Exception();
		} finally {
			try {
				if (ps != null)
					ps.close();
			} catch (SQLException ex) {
				ex.printStackTrace();
//				MyLog.loggingError(ex.getMessage());
//				MyLog.logging(ex.getMessage());
				throw new Exception();
			} finally {
				try {
					if (conn != null)
						conn.close();
				} catch (SQLException exp) {
					exp.printStackTrace();
//					MyLog.loggingError(exp.getMessage());
//					MyLog.logging(exp.getMessage());
					throw new Exception();
				}
			}
		}
	}

	// 向数据库插入数据，批处理
	public static void insertBatch() throws Exception {
		try {
			DBUtil db = new DBUtil();
			oracleConn = db.getConnection();
			oraclePs = oracleConn.prepareStatement(insertSQL);
			// 关闭默认提交
			oracleConn.setAutoCommit(false);
			long start = System.currentTimeMillis();

			for (int i = 1; i <= allCount; i++) {
				oraclePs.setString(1, "firstname" + i);
				oraclePs.setString(2, "lastname" + i);
				oraclePs.setInt(3, 23);
				// 加入批处理计划中
				oraclePs.addBatch();
				if ((i % preCount) == 0) {
					// 执行批处理
					oraclePs.executeBatch();
					System.out.println("当前进行完毕===>" + (++count) * 1.0
							* preCount / allCount * 100 + "%");
				}
			}
			// 把剩余未提交的批处理提交
			oraclePs.executeBatch();
			long end = System.currentTimeMillis();
			System.out.println("数据导入完毕,所用时间为: " + (end - start) + " ms");
		} catch (Exception e) {
			oracleConn.rollback();
			System.out.println("数据出错,已进行回滚");
			e.printStackTrace();
//			MyLog.loggingError(e.getMessage());
//			MyLog.logging(e.getMessage());
			throw new Exception();
		} finally {
			// 这句要不要无所谓。数据库都会插入成功
			oracleConn.commit();
			DBUtil.release(rs, oraclePs, oracleConn);
		}
	}

	// 取ID值
	public static int getId(String tableName) throws Exception {
		int sequence = 0;
		String seq = "select decode(max(id)+1,'',1,max(id)+1) from "
				+ tableName;
		try {
			DBUtil db = new DBUtil();
			oracleConn = db.getConnection();
			oraclePs = oracleConn.prepareStatement(seq);
			rs = oraclePs.executeQuery();
			if (rs.next()) {
				sequence = (int) rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
//			MyLog.loggingError(e.getMessage());
//			MyLog.logging(e.getMessage());
			throw new Exception();
		} finally {
			DBUtil.release(rs, oraclePs, oracleConn);
		}
		return sequence;
	}

	public Connection getConnection() {
		/** 声明Connection连接对象 */
		try {
			// 加载JDBC驱动
			Class.forName(className);
			// 创建数据库连接
			Connection con = DriverManager.getConnection(url, name, password);
			return con;
		} catch (Exception e) {
			System.out.println("Failed to load JDBC/ODBC driver.");
			return null;
		}

	}

	/**
	 * <br>
	 * 方法说明：执行查询SQL语句 <br>
	 * 输入参数：Connection con 数据库连接 <br>
	 * 输入参数：String sql 要执行的SQL语句 <br>
	 * 输入参数：String returnString 查询的字段 <br>
	 * 返回类型：
	 */
	@SuppressWarnings("unchecked")
	public HashMap queryMap(Connection con, String sql) {
		PreparedStatement stmt;
		ResultSet rs;
		//int result=0;

		HashMap map = new HashMap();
		try {
			// 声明语句
			stmt = con.prepareStatement(sql);
			// 执行查询
			rs = stmt.executeQuery();
			ResultSetMetaData rmeta = rs.getMetaData();
			// 获得数据字段个数
			int numColumns = rmeta.getColumnCount();
			//int ii = rs.getRow();
			while (rs.next()) {
				for (int i = 0; i < numColumns; i++) {
					String columnName = rmeta.getColumnName(i + 1);
					String sTemp = rs.getString(columnName);
					//System.out.println("sTemp== "+sTemp);
					if (sTemp == null || sTemp == "") {
						sTemp = " ";
					}
					map.put(columnName, sTemp);
				}
				System.out.println("");
			}
			rs.close();
			stmt.close();
			con.close();
		} catch (Exception e) {
			rs = null;
			stmt = null;
			con = null;
			System.out.println("query error:" + e);
			e.printStackTrace();
		}
		return map;
	}

	@SuppressWarnings("unchecked")
	public List<HashMap> queryList(Connection con, String sql) {
		PreparedStatement stmt;
		ResultSet rs;
		//int result=0;

		List<HashMap> list = new ArrayList<HashMap>();
		try {
			// 声明语句
			stmt = con.prepareStatement(sql);
			// 执行查询
			rs = stmt.executeQuery();
			ResultSetMetaData rmeta = rs.getMetaData();
			// 获得数据字段个数
			int numColumns = rmeta.getColumnCount();
			//int ii = rs.getRow();
			HashMap map = null;
			while (rs.next()) {
				map = new HashMap();
				for (int i = 0; i < numColumns; i++) {
					String columnName = rmeta.getColumnName(i + 1);
					String sTemp = rs.getString(columnName);
					//System.out.println("sTemp== "+sTemp);
					if (sTemp == null || sTemp == "") {
						sTemp = " ";
					}

					map.put(columnName, sTemp);
				}
				list.add(map);
				//System.out.println("");
			}
			rs.close();
			stmt.close();
			con.close();
		} catch (Exception e) {
			rs = null;
			stmt = null;
			con = null;
			System.out.println("query error:" + e);
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * <br>
	 * 方法说明：执行插入、更新、删除等没有返回结果集的SQL语句 <br>
	 * 输入参数：Connection con 数据库连接 <br>
	 * 输入参数：String sql 要执行的SQL语句 <br>
	 * 返回类型：
	 */
	public int execute(Connection con, String sql) {
		int result = -1;
		PreparedStatement stmt;
		try {
			con.setAutoCommit(false);
			stmt = con.prepareStatement(sql);
			result = stmt.executeUpdate();
			con.commit();
			stmt.close();
			con.close();
		} catch (Exception e) {
			result = -1;
			stmt = null;
			con = null;
			System.out.println("execute error: sql = " + sql);
			e.printStackTrace();
			System.out.println(e);
		}
		return result;
	}
//	public static void main(String[] args) throws SQLException, IOException {
//		// Gather gather = new Gather();
//		// gather.setId(3);
//		// gather.setXmmc("abc");
//		// gather.setXmzt(2);
//		// DBUtil.insertGather(gather);
//		// Basic basic = new Basic();
//		// basic.setId(1);
//		// basic.setGid(2);
//		// basic.setKgrq("2005-12-18");
//		// basic.setJgrq("2012-06-15");
//		// DBUtil.insertBasic(basic);
//		// Date date = new Date(2000, 11, 22);
//		// System.out.println(date);
////		Gather gather = new Gather();
////		gather.setId(1);
////		gather.setXmmc("a");
////		gather.setXmzt(2);
//		try {
//			Gather gather = new Gather();
//			gather.setId(1);
//			gather.setXmmc("a");
//			gather.setXmzt(2);
//			DBUtil db = new DBUtil();
//			oracleConn = db.getConnection();
//			db.updateGather(gather);
////			Field[] fs = gather.getClass().getDeclaredFields();
////			for(Field fii: fs) {
////				System.out.println(fii.getName());
////			}
////			DBUtil db = new DBUtil();
////			oracleConn = DBUtil.getConnection();
////		Basic basic = new Basic();
////		basic.setXmmc("中星恬园");
////		basic.setJsdw("上海中星集团新城房产有限公");
////			System.out.println(db.getFlag(basic));
//		}
//		catch(Exception e) {
//			e.printStackTrace();
//		}
//	}
}

